﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    /// <summary>
    /// 先进设备
    /// </summary>
    public class WorkShopService : BaseService
    {
        /// <summary>
        /// 添加先进设备
        /// </summary>
        /// <param name="name">图片名称</param>
        /// <param name="nameEn">图片名称(En)</param>
        /// <param name="description">图片描述</param>
        /// <param name="descriptionEn">图片描述(En)</param>
        /// <param name="smallImge">小图片</param>
        /// <param name="normalImage">正常图片</param>
        /// <param name="bigImage">大图片</param>
        /// <param name="sort">排序</param>
        /// <param name="filedata"> </param>
        /// <returns></returns>
        public int Add(string name, string nameEn, string description, string descriptionEn, string smallImge, string normalImage, string bigImage, int sort, HttpPostedFileBase filedata = null)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            var sb = new StringBuilder();
            sb.Append("insert into tbWorkShop(");
            sb.Append("CoNormalImage,CoSmallImage,CoBigImage,CoName,CoDescription,CoNameEn,CoDescriptionEn,CoSort");
            sb.Append(") values(");
            sb.Append("@CoNormalImage,@CoSmallImage,@CoBigImage,@CoName,@CoDescription,@CoNameEn,@CoDescriptionEn,@CoSort");
            sb.Append(")");
            var parameter = new[]
                                {
                                    new OleDbParameter("@CoNormalImage", OleDbType.VarChar,255),
                                    new OleDbParameter("@CoSmallImage", OleDbType.VarChar,255),
                                    new OleDbParameter("@CoBigImage", OleDbType.VarChar,255),
                                    new OleDbParameter("@CoName", OleDbType.VarChar,255),
                                    new OleDbParameter("@CoDescription", OleDbType.LongVarWChar),
                                    new OleDbParameter("@CoNameEn", OleDbType.VarChar,255),
                                    new OleDbParameter("@CoDescriptionEn", OleDbType.LongVarWChar),
                                    new OleDbParameter("@CoSort", OleDbType.Integer)
                                };
            parameter[0].Value = normalImage ?? string.Empty;
            parameter[1].Value = smallImge ?? string.Empty;
            parameter[2].Value = bigImage ?? string.Empty;
            parameter[3].Value = name;
            parameter[4].Value = description;
            parameter[5].Value = nameEn;
            parameter[6].Value = descriptionEn;
            parameter[7].Value = sort;
            if (filedata != null && filedata.ContentLength > 0)
            {
                var uploadHelper = new UploadHelper
                {
                    PostedFile = filedata,
                    RelativePath = WorKShopConfig.RelativePath,
                    IsAdaptive = WorKShopConfig.IsAdaptive,
                    IsImage = true,
                    IsGenerateThumbnail = WorKShopConfig.IsGenerateThumbnail,
                    FileMaxLength = WorKShopConfig.ImageSize,
                    ThumbnailHeight = WorKShopConfig.ThumbnailHeight,
                    ThumbnailWidth = WorKShopConfig.ThumbnailWidth,
                    ThumbnailPrefix = WorKShopConfig.ThumbnailPrefix
                };
                if (!uploadHelper.Save())
                {
                    throw new Exception(uploadHelper.ErrorMessage);
                }
                string fileName = uploadHelper.FileName ?? string.Empty;
                string thumFileName = uploadHelper.ThumbnailFileName ?? string.Empty;
                parameter[0].Value = fileName;
                parameter[1].Value = thumFileName;
                parameter[2].Value = fileName;
            }
            var id = DbHelperOleDb.Add(sb.ToString(), parameter).SafeToInt();
            if (id > 0)
            {
                return id;
            }
            throw new Exception("添加失败！");
        }

        /// <summary>
        /// 修改先进设备
        /// </summary>
        /// <param name="id">先进设备ID</param>
        /// <param name="name">图片名称</param>
        /// <param name="nameEn">图片名称(En)</param>
        /// <param name="description">图片描述</param>
        /// <param name="descriptionEn">图片描述(En)</param>
        /// <param name="smallImge">小图片</param>
        /// <param name="normalImage">正常图片</param>
        /// <param name="bigImage">大图片</param>
        /// <param name="sort">排序</param>
        /// <param name="filedata"> </param>
        /// <returns></returns>
        public int Eidt(int id, string name, string nameEn, string description, string descriptionEn, string smallImge, string normalImage, string bigImage, int sort, HttpPostedFileBase filedata = null)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            var sbSql = new StringBuilder();
            sbSql.Append("update tbWorkShop set ");
            sbSql.Append("CoName=@CoName,CoDescription=@CoDescription,CoNameEn=@CoNameEn,CoDescriptionEn=@CoDescriptionEn");
            sbSql.Append(",CoNormalImage=@CoNormalImage,CoSmallImage=@CoSmallImage,CoBigImage=@CoBigImage,CoSort=@CoSort");
            sbSql.AppendFormat(" where CoWorkShopId={0}", id);
            var parameters = new[]
                {

                    new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescription", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescriptionEn", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoNormalImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSmallImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoBigImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSort", OleDbType.Integer),
                    new OleDbParameter("@id", OleDbType.Integer)
                };
            parameters[0].Value = name;
            parameters[1].Value = description;
            parameters[2].Value = nameEn;
            parameters[3].Value = descriptionEn;
            parameters[4].Value = normalImage ?? string.Empty;
            parameters[5].Value = smallImge ?? string.Empty;
            parameters[6].Value = bigImage ?? string.Empty;
            parameters[7].Value = sort;
            parameters[8].Value = id;

            if (filedata != null && filedata.ContentLength > 0)
            {
                var uploadHelper = new UploadHelper
                {
                    PostedFile = filedata,
                    RelativePath = WorKShopConfig.RelativePath,
                    IsAdaptive = WorKShopConfig.IsAdaptive,
                    IsImage = true,
                    IsGenerateThumbnail = WorKShopConfig.IsGenerateThumbnail,
                    FileMaxLength = WorKShopConfig.ImageSize,
                    ThumbnailHeight = WorKShopConfig.ThumbnailHeight,
                    ThumbnailWidth = WorKShopConfig.ThumbnailWidth,
                    ThumbnailPrefix = WorKShopConfig.ThumbnailPrefix
                };
                if (!uploadHelper.Save())
                {
                    throw new Exception(uploadHelper.ErrorMessage);
                }
                var fileName = uploadHelper.FileName ?? string.Empty;
                var thumFileName = uploadHelper.ThumbnailFileName ?? string.Empty;
                parameters[4].Value = fileName;
                parameters[5].Value = thumFileName;
                parameters[6].Value = fileName;
                var path = HttpContext.Current.Server.MapPath(PhotoConfig.RelativePath);
                var ds = DbHelperOleDb.Query("select * from tbWorkShop where CoWorkShopId={0}".FormatWith(id));
                foreach (DataRow item in ds.Tables["ds"].Rows)
                {
                    FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
                }
            }

            var rows = DbHelperOleDb.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            if (rows > 0)
            {
                return rows;
            }
            throw new Exception("修改失败！");
        }

        /// <summary>
        /// 删除先进设备
        /// </summary>
        /// <param name="id">先进设备ID</param>
        public void Delete(int id)
        {
            var path = HttpContext.Current.Server.MapPath(WorKShopConfig.RelativePath);
            var ds = DbHelperOleDb.Query("select * from tbWorkShop where CoWorkShopId={0}".FormatWith(id));
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            int rows = DbHelperOleDb.ExecuteSql("delete from tbWorkShop where CoWorkShopId={0}".FormatWith(id));
            if (!(rows > 0))
                throw new Exception("记录不存在！");
        }

        public void BulkDelete(int[] ids)
        {
            var path = HttpContext.Current.Server.MapPath(WorKShopConfig.RelativePath);
            var ds = DbHelperOleDb.Query("select * from tbWorkShop where CoWorkShopId in ({0})".FormatWith(ids.ToString()));
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            int rows = DbHelperOleDb.ExecuteSql("delete from tbWorkShop where CoWorkShopId in ({0})".FormatWith(ids.ToString()));
            if (!(rows > 0))
                throw new Exception("记录不存在！");
        }

        /// <summary>
        /// 移除先进设备图片
        /// </summary>
        /// <param name="id">先进设备ID</param>
        /// <returns></returns>
        public int DeletePicture(int id)
        {
            var path = HttpContext.Current.Server.MapPath(WorKShopConfig.RelativePath);
            var ds = DbHelperOleDb.Query("select * from tbWorkShop where CoWorkShopId={0}".FormatWith(id));
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            var sbSql = new StringBuilder();
            sbSql.Append("update tbWorkShop set CoNormalImage=@CoNormalImage,CoSmallImage=@CoSmallImage,CoBigImage=@CoBigImage where CoWorkShopId=@id");
            var parameters = new[]
                                {
                                    new OleDbParameter("@id", OleDbType.Integer),
                                    new OleDbParameter("@CoNormalImage", OleDbType.VarChar, 255),
                                    new OleDbParameter("@CoSmallImage", OleDbType.VarChar, 255),
                                    new OleDbParameter("@CoBigImage", OleDbType.VarChar, 255)
                                };
            parameters[0].Value = id;
            parameters[1].Value = "";
            parameters[2].Value = "";
            parameters[3].Value = "";
            int rows = DbHelperOleDb.ExecuteSql(sbSql.ToString(), parameters);
            return rows;
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns>Table 返回一张表格</returns>
        public Table GetTable(string name, int pageValue = 1, int rowsValue = 10, string sort = "CoSort", string order = "Desc")
        {
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where (CoName like '%'+@CoName+'%' or  CoNameEn like '%'+@CoName+'%') ");
            var parameter = new[]
                           {
                             new OleDbParameter("@CoName", OleDbType.VarChar, 255)
                           };
            parameter[0].Value = name.SqlLike();
            int total = DbHelperOleDb.GetSingle("select count(*) from TbWorkShop {0}".FormatWith(sbSqlWhere), parameter).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} * from  TbWorkShop {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and CoWorkShopId not in (select top {0} CoWorkShopId from TbWorkShop {1} order by {2} {3},coworkshopid desc) ", rowsValue * (pageValue - 1), sbSqlWhere, sort, order);
            sbSql.AppendFormat(" order by {0} {1},coworkshopid desc", sort, order);
            var ds = DbHelperOleDb.Query(sbSql.ToString(), parameter);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total,
                DataTable = ds.Tables["ds"]
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperOleDb.Query("select *  from TbWorkShop where CoWorkShopId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }

        /// <summary>
        /// 获取表格
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            return DbHelperOleDb.Query("select * from TbWorkShop order by cosort desc").Tables["ds"];
        }
    }
}
